﻿using System;
using System.Collections.Generic;
using System.Text;
using ConnectLib.DAO;
using MSSQLProvider.DataBase;

namespace ConnectLib.DAL
{
    public class DeliveryGroupDal : MSSQLDataAccess
    {
        private static DeliveryGroupDal _instance = new DeliveryGroupDal();

        public static DeliveryGroupDal Instance
        {
            get
            {
                return _instance;
            }
        }

        private bool IsDeliveryGroupDuplicate(string delid)
        {
            if (ExecuteScalar(string.Format("SELECT DelId FROM [DeliveryGroup] WHERE InvId = '{0}'", delid)) != null)
            {
                return true;
            }

            return false;
        }

        public int AddDeliveryGroup(DeliveryGroupDao delgrp)
        {
            try
            {
                if (!IsDeliveryGroupDuplicate(delgrp.DelId))
                {
                    return 0;
                }

                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("DelId");
                arrParamName.Add("@DelId");
                arrValue.Add(delgrp.DelId);

                arrFielName.Add("InvIds");
                arrParamName.Add("@InvIds");
                arrValue.Add(delgrp.InvIds);

                arrFielName.Add("LocationDate");
                arrParamName.Add("@LocationDate");
                arrValue.Add(delgrp.LocationDate);

                arrFielName.Add("LocationAt");
                arrParamName.Add("@LocationAt");
                arrValue.Add(delgrp.LocationAt);

                var queryBuider = new StringBuilder();
                queryBuider.Append("INSERT INTO [DeliveryGroup] (");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    queryBuider.Append(arrFielName[i]);
                    if (i < arrFielName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");
                queryBuider.Append(" VALUES (");
                i = 0;

                for (; i < arrParamName.Count; i++)
                {
                    queryBuider.Append(arrParamName[i]);
                    if (i < arrParamName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateTracking(DeliveryGroupDao delgrp)
        {
            try
            {
                if (!IsDeliveryGroupDuplicate(delgrp.DelId))
                {
                    return 0;
                }

                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("InvId");
                arrParamName.Add("@InvId");
                arrValue.Add(delgrp.InvIds);

                arrFielName.Add("LocationDate");
                arrParamName.Add("@LocationDate");
                arrValue.Add(delgrp.LocationDate);

                arrFielName.Add("LocationAt");
                arrParamName.Add("@LocationAt");
                arrValue.Add(delgrp.LocationAt);

                arrFielName.Add("DelId");
                arrParamName.Add("@DelId");
                arrValue.Add(delgrp.DelId);

                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [DeliveryGroup] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateLocation(string delid, DateTime locationtime, string locationat)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("LocationDate");
                arrParamName.Add("@LocationDate");
                arrValue.Add(locationtime);

                arrFielName.Add("LocationAt");
                arrParamName.Add("@LocationAt");
                arrValue.Add(locationat);

                arrFielName.Add("DelId");
                arrParamName.Add("@DelId");
                arrValue.Add(delid);

                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [DeliveryGroup] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateInvoice(string delid, string invids)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("InvIds");
                arrParamName.Add("@InvIds");
                arrValue.Add(invids);

                arrFielName.Add("DelId");
                arrParamName.Add("@DelId");
                arrValue.Add(delid);

                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [DeliveryGroup] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool DeleteDeliveryGroup(string delid)
        {
            return ExecuteNonQuery(string.Format("DELETE FROM [DeliveryGroup] WHERE DelId = '{0}'", delid));
        }

        public List<DeliveryGroupDao> GetDeliveryGroup(string delid)
        {
            try
            {
                var lsHtDelgrp = ExecuteArrayHastable(string.Format("SELECT * FROM [DeliveryGroup] WITH(NOLOCK) WHERE DelId = '{0}'", delid));

                if (lsHtDelgrp != null && lsHtDelgrp.Count > 0)
                {
                    var lsDelgrp = new List<DeliveryGroupDao>(lsHtDelgrp.Count);

                    foreach (var delgrp in lsHtDelgrp)
                    {
                        lsDelgrp.Add(new DeliveryGroupDao(delgrp));
                    }

                    return lsDelgrp;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

            return null;
        }

    }
}
